<?php
namespace PI;


class DbException extends \Exception{
     // 重定义构造器使 message 变为必须被指定的属性
     public function __construct($message, $code = 0, \Exception $previous = null) {
        // 自定义的代码

        // 确保所有变量都被正确赋值
        parent::__construct($message, $code, $previous);
    }
}
class DbParameter{
    public $value;
    public $type;
    public function __construct($value,$type=null){
        $this->value = $value;
        $this->type = $type?gettype($value):$type;
    }
}
class DbReference{
    public $primary_key;
    public $primary_table;
    public $foreign_key;
    public $foreign_table;
    public $primary_propname;
    public $link_table;
    public $left_key;
    public $right_key;
    public $foreign_class;
    public $type;
    public $fieldnames;
}
class DbJoin{
    public $propname;
    public $reference;
    public $fields;
    public function __construct(string $prop,DbReference $ref,array $fieldnames=null)
    {
        $this->propname = $prop;
        $this->reference = $ref;
        $all_fields = $ref->$fieldnames;//$ref->$fieldnames?$ref->$fieldnames:__CLASS__::$DB_FIELDNAMES;
        if($fieldnames){
            $fields = [];
            foreach($fieldnames as $fname){
                if(in_array($fname,$all_fields)) $fields[] = $fname;
            }
        }else {
            $this->fields = $fieldnames;
        }
    }
}
class DbSet  implements \Iterator{
    static $DB_FIELDNAMES;
    static $DB_PRIMARY;
    static $DB_REFERENCES;

    

    private $_parameters;
    private $_tblname_;
    private $_where_;
    private $_allowedFields_;
    private $_deniedFields_;
    private $_fields_;
    protected $_current_;
    private $_datas_;
    private $_joins_;
    private $_take_;
    private $_skip_;
    private $_single_;

    private $_database_;

    public function __construct(Database $database,string $tablename=null,string $where=null,...$args){
        $this->_database_ = $database;
        if(!$tablename){
            $tablename = str_replace("DbSet","",__CLASS__);
        }
        $this->_tblname_ = $tablename;
        if($where) $this->where($where,$args);
        
    }
    public function get_connection(){
        $conn = $this->_conn_;
        if(!$conn) $this->_conn_ = Database::create();
    }

    public function take($count):DbSet{
        $this->_take_ = $count; return $this;
    }
    public function skip($count):DbSet{
        $this->_skip_ = $count;return $this;
    }

    public function where(string $where,...$args):DbSet{
        if($this->_datas_) throw new DbException("已经加载了数据，不可以再修改条件");
        $this->_where_ = $where;
        if($args){
            if(!$this->_parameters) $this->_parameters = [];
            foreach($args as $arg){
                $this->_parameters[] = new DbParameter($arg);
            }
        }
        return $this;
    }
    public function set_parameters(array $values):DbSet{
        $this->_parameters=[];
        foreach($values as $arg){
            $this->_parameters[] = new DbParameter($arg);
        }
        return $this;
    }

    
    public function join(string $prop,array $fieldnames=null){
       $refs = static::$DB_REFERENCES;
       if(!isset($refs[$prop])) throw new DbException("未定义的外键对象，无法关联，请检查".__CLASS__."::\$DB_REFERENCES");
       $ref = $refs[$prop];
       if(!$this->_joins_) $this->_joins_=[$prop=>new DbJoin($prop,$ref,$fieldnames)];
       $this->_joins_[$prop] = new DbJoin($prop,$ref,$fieldnames);
    }

    function add($obj=null):DbSet{
        if($obj) $this->set($obj);
        if(!$this->_current_) return $this;
        $isConnCreated = false;
        $conn = $this->_database_;
        if(!$conn->is_connected) $conn->open();
        
        $keys = "";
        $values= "";
        $params  =[];
        $fields = [];
        foreach($this->_current_ as $k=>$v){
            if(!$this->check_field($k)) continue;
            if($keys) $keys.=",";
            if($values) $values .=",";
            $keys.= $conn->make_fieldname($k);
            $values .= "?";
            $params[] = new DbParameter($v);
            $fields[] = $k;
        }
        $this->_fields_ = $fields;

        $tablename = $conn->make_tablename($this->_tblname_);
        $sql ="INSERT INTO $tablename ($keys) VALUES ($values)";
        
        $cmd = $conn->create_command($sql);
        $cmd->params = $params;
        $cmd->execute(); 
        if($cmd->insert_id && static::$DB_PRIMARY) {
            $this->_current_[static::$DB_PRIMARY]= $cmd->insertId;
        }
        $cmd->close();
        $cmd =null;
        if($isConnCreated){ $conn->close(); $conn=null;}
        return $this;
    }

    function query_count():int{
        $conn = $this->_database_;
        if(!$conn->is_connected) $conn->open();
        
        $tablename = $conn->make_tablename($this->_tblname_);
        $sql = "SELECT count(*) FROM $tablename";
        if($this->_where_) $sql .= "WHERE $this->_where_";

        $rs = null;
        if($this->_parameters){
            $cmd = $conn->create_command($sql);
            $cmd->params = $this->_parameters;
            $rs = $cmd->first(false);
            $cmd->close();
            $cmd = null;
        }else {
            $rs = $conn->first($sql,false);
        }
        
        return $rs[0];
    }

    function load($id=null):DbSet{
        if($this->_current_) throw new DbException("已经加载过数据，不能再次加载");
        $conn = $this->_database_;
        if(!$conn->is_connected) $conn->open();
        $fieldnames = $this->make_sqlFields($conn);
        $tablename = $conn->make_tablename($this->_tblname_);
        $many_joins = [];
        if($this->_joins_){
            
            foreach($this->_joins_ as $k =>$join){
                $ref = $join->reference;
                if($ref->type=="many" ){
                    $many_joins[] = $join;
                    if($ref->link_table){
                        $link_tbname = $conn->make_tablename($ref->link_table);
                        $joinedTablename = $conn->make_tablename($join->join_class);
                        $tablename .=" LEFT JOIN $link_tbname  ON $tablename.$ref->primary_key = $link_tbname.$ref->left_key";
                        $tablename .= "LEFT JOIN $joinedTablename $k ON $link_tbname.$ref->right_key=$k.$ref->foreign_key";
                    }else {
                        $joinedTablename = $conn->make_tablename($join->join_class);
                        $tablename .= "LEFT JOIN $joinedTablename $k ON $tablename.$ref->primary_key=$k.$ref->foreign_key";
                    }
                   
                }else {
                    $joinedTablename = $conn->make_tablename($join->join_class);
                    $tablename .= "LEFT JOIN $joinedTablename $k ON $tablename.$ref->primary_key=$k.$ref->foreign_key";
                }
            }
        }
        $where = $this->_where_;
        if($id!==null){
            $prim = static::$DB_PRIMARY?static::$DB_PRIMARY:"Id";
            $where = $prim."=?";
            $this->_where_ = $where;
            $this->_parameters = [new DbParameter($id)];
        }
        $sql = $conn->create_select_sql($tablename,$fieldnames,$where,$this->_take_, $this->_skip_);
        
        $datas=null;
        $fetch_assoc = !$this->_fields_ || !$this->_joins_;
        if($this->_parameters){
            $cmd = $conn->create_command($sql,$id); 
            if($id===null)$cmd->params = $this->_parameters;
            $datas = $cmd->query($fetch_assoc?null:false);
            $cmd->close();
            $cmd = null;
        }else {
            $datas = $conn->query($sql,$fetch_assoc?null:false);
        }
        if(!$this->_fields_ || !$this->_joins_){
            $this->_datas_ = $datas;
        }else {
            $this->_datas_ = $datas?$this->_fillData($datas):[];
        }
        
        if(count($this->_datas_)){
            //$this->_current_ = $this->_datas_[0];
            if($many_joins){
                throw new \Exception("Not implement");
            }
        }
        $this->_single_ =$id!==null;
        return $this;
    }

    private function _fillData($rs){
        $datas = [];
        $last_id = null;
        $current_entity = null;
        $last_ids=[];
        foreach($rs as $row){
            $index = 0;
            $entity=[];
            $id = null;
            foreach($this->_fields_ as $field){
                $value = $row[$index];
                $entity[$field] = $value;
                if($field===static::$DB_PRIMARY) {
                    $id= $value;
                    //一对多时，前面重复的部分,不要
                    if($id===$last_id){
                        $entity = null;
                        $index = count($this->_fields_);
                    }
                }
                if($entity==null)break;
                $index++;
            }
            if($entity!=null){
                $last_id = $id;
                $datas[]= $current_entity;
                $current_entity = &$datas[count($datas)-1];
            }
            if($this->_joins_){
                $joinedEntity=[];
                $joinedIndex = 0;
                
                foreach($this->_joins_ as $ref_name =>$join){
                    $ref = $join->reference;
                    $join_fields = $join->fields;
                    $joined_id = null;
                    $joinedFieldIndex= 0;
                    foreach($join_fields as $field){
                        $value = $row[$index];
                        $joinedEntity[$field] = $value;
                        if($field===$ref->foreign_id) {
                            $joined_id= $value;
                            //一对多时，前面重复的部分,不要
                            if($joined_id===$last_ids[$joinedIndex]){
                                $joinedEntity = null;
                                $index += count($join_fields)-$joinedIndex;
                            }
                        }
                        if($joinedEntity===null) break;
                        $index++;
                        $joinedFieldIndex++;
                    }
                    if($joinedEntity){
                        if($ref->type==="many"){
                            $joinedEntities = &$entity[$ref_name];
                            if(!$joinedEntities){
                                $entity[$ref_name]= [];
                                $joinedEntities = &$entity[$ref_name];
                            }
                            $joinedEntities[] = $joinedEntity;
                        }else {
                            $entity[$ref_name]= $joinedEntity;
                        }
                    }
                    $joinedIndex++;

                }
            }
        }
        return $datas;
    }

    private function _loadMany(array $joins){

    }

    function to_array(){
        if($this->_datas_===null) $this->load();
        return $this->_datas_;
    }
    function to_object($target=null){
        if(is_string($target)) $target = new $target();
        if(!$this->_datas_) return $target;
        $row = $this->_datas_[0];
        foreach($row as $k=>$v) $target->$k = $v;
        return $target;
    }
    function set($obj):DbSet{
        $this->_current_ = [];
        foreach($obj as $k=>$v) $this->_current_[$k] = $v;
        return $this;
    }

    function save($obj=null):DbSet{
        if($obj) $this->set($obj);
        if(!$this->_current_) return $this;
        $conn = $this->_database_;
        if(!$conn->is_connected) $conn->open();
        $tablename = $conn->make_tablename($this->_tblname_);

        $sql ="UPDATE $tablename ";
        $sets = "";
        $params  =[];
        $fields=[];
        //$prim = static::$DB_PRIMARY?static::$DB_PRIMARY:"Id";
        foreach($this->_current_ as $k=>$v){
            //if($k==$prim) continue;
            if(!$this->check_field($k)) continue;
            if($sets) $sets.=",";
            $sets.= $conn->make_fieldname($k)."=?";
            $params[] = new DbParameter($v);
            $fields[]= $k;
        }
        $this->_fields_ = $fields;
        $sql.=" SET $sets";
        if($this->_where_) $sql.=" WHERE ".$this->_where_;
        if($this->_parameters){
            foreach($this->_parameters as $par){
                $params[] = $par;
            }
        }
        $cmd = $conn->create_command($sql);
        $cmd->params = $params;
        $cmd->execute();
        $cmd->close();
        return $this;
    }

    function remove():DbSet{
        $conn = $this->_database_;
        if(!$conn->is_connected) $conn->open();
        $tablename = $conn->make_tablename($this->_tblname_);
        $where = $this->_where_;
        
        if(!$where && $this->_current_ && static::$DB_PRIMARY){
            $id = $this->_current_[static::$DB_PRIMARY];
            if($id){
                $where = static::$DB_PRIMARY."=?";
                $this->_parameters =[new DbParameter($id)];
            }
        }
        if(!$where) throw new DbException("没有设置where，无法删除");
        $sql = "DELETE FROM $tablename WHERE ".$where;
        $cmd = $conn->create_command($sql);
        $cmd->params = $this->_parameters;
        $cmd->execute();
        $cmd->close();
        return $this;
    }

    

    protected function make_sqlFields(Database $conn):string{
        
        $allows = $this->_allowedFields_ ?$this->_allowedFields_ : static::$DB_FIELDNAMES;
        if(!$allows) return "*";
        $fs = "";
        $fields = [];
        foreach($allows as $fname){
            if($this->_deniedFields_ && in_array($fname,$this->_deniedFields_)) continue;
            if($fs) $fs.=",";
            $fs.= $conn->make_fieldname($fname);
            $fields[] = $fname;
        }
        $this->_fields_ = $fields;
        if($this->_joins_){
            foreach($this->_joins_ as $prop => $join){
                $ref = $join->reference;
                if($ref->type==="many") continue;
                $flds = $join->fields;
                foreach($flds as $fld){
                    if($fs) $fs.=",";
                    $db_fieldname = $conn->make_fieldname($prop."_$fld");
                    $fs.="$prop.".$conn->make_fieldname($fld)." $db_fieldname";
                }
            }
        }
        return $fs;
    }

    public function check_field(string $name):bool{
        if($this->_deniedFields_ && in_array($name,$this->_deniedFields_)) return false; 
        $allows = $this->_allowedFields_ ?$this->_allowedFields_ : static::$DB_FIELDNAMES;
        if(!$allows) return true;
        return in_array($name,$allows);

    }



    public function allowed_fields(array $args=null):DbSet{
        $this->_allowedFields_ = $args;
        return $this;
    }

    public function denied_fields(array $args=null):DbSet{
        $this->_deniedFields_ = $args;
        return $this;
    }

    function to_json($options=0){
        if($this->_datas_===null) $this->load();
        if($this->_single_) {
            if($this->_datas_) return json_encode($this->_datas_[0],$options);
            else return json_encode($this->_current_,$options);
        }
        else return json_encode($this->_datas_!==null?$this->_datas_:[$this->_current_],$options);
    }

    function __set(string $name,$value){
        if(!$this->_current_) $this->_current_ = [];
        $this->_current_[$name] = $value;

        if($this->_datas_){
            foreach($this->_datas_ as &$row){
                $row[$name] = $value;
            }
        }
        return $this;
    }

    function __get(string $name){
        if($this->_current_ && isset($this->_current_[$name])) return $this->_current_[$name];
        if(!$this->_datas_){
            if(!$this->_where_) throw new DbException("即未赋值，也因为缺乏where而无法加载数据，不可以获取属性值");
            $this->load();
        }
        $rows = $this->_datas_;
        if($rows) {
            $row = $rows[0];
            if(isset($row[$name]))return $row[$name];
        }
        return null;
    }

    function __isset(string $name){
        if(!$this->_current_) return false;
        return isset($this->_current_[$name]);
    }

    function __unset(string $name){
        if(!$this->_current_) unset($this->_current_[$name]);
        if($this->_datas_){
            foreach($this->_datas_ as $row){
                unset($row[$name]);
            }
        }
        return $this;
    }

    public function rewind() { if($this->_datas_===null) $this->load();reset($this->_datas_); }
    public function current() { return current($this->_datas_); }
    public function key() { return key($this->_datas_); }
    public function next() { return next($this->_datas_); }
    public function valid() { return ( $this->current() !== false ); }

}


class Database{
    public $host;
    public $user;
    public $password;
    public $dbname;
    public $connection;
    public $error;
    public $affectedCount;
    public $is_connected;
    public function __construct($dbHost,$dbUser=null,$dbPassword=null,$dbDbname=null){
        $this->host = $dbHost;
        $this->user= $dbUser;
        $this->password = $dbPassword;
        $this->dbname = $dbDbname; 
    }
    function open():Database{
        if($this->is_connected) throw new DbException("数据库连接已经打开，请不要重复打开");
        $this->connection = new \mysqli($this->host, $this->user, $this->password, $this->dbname);
        if($this->connection->connect_error) throw new DbException($this->connection->connect_error);
        $this->connection->select_db($this->dbname);
        if($this->connection->error) throw new DbException($this->connection->error);
        $this->is_connected=true;
        return $this;
    }
    function close():Database{
        $this->connection->close();
        $this->connection = null;
        $this->is_connected = false;
        return $this;
    }
    function beginTrans():Database{
        $this->connection->begin_transaction();
        $this->error = $this->connection->error;
        return $this;
    }
    function commit():Database{
        $this->commit(); 
        $this->error = $this->connection->error;
        return $this;
    }
    function rollback():Database{
        $this->rollback(); 
        $this->error = $this->raw->error;
        return $this;
    }
    function execute($sql):Database{
        $rs = $this->connection->query($sql);
        if($this->connection->error) throw new DbException($this->connection->error);
        $this->affectedCount = $this->connection->affected_rows;
        return $rs;
    }
    function query($sql,bool $rs_type=null){
        $rs = $this->connection->query($sql);
        if(!$rs) throw new DbException($this->connection->error);
        $results= array();
        if($rs_type===null){
            while ($row = $rs->fetch_assoc()) $results[] = $row;
            
        }else if($rs_type===false){
            while ($row = $rs->fetch_row()) $results[] = $row;
        }else {
            while ($row = $rs->fetch_object()) $results[] = $row;
        }
        return $results;
    }
    function first($sql,$type=null){
        $rs = $this->connection->query($sql);
        if(!$rs) {$this->error = $this->connection->error; return null;}
        
        if($type===null){
            return $rs->fetch_assoc();
        }else if($type===false){
            return $rs->fetch_row();
        }else {
            return $rs->fetch_object();
        }
        
    }

    function create_select_sql(string $tbs,string $fieldnames,string $where,int $take=null,int $skip=null):string{
        $sql = "SELECT $fieldnames FROM $tbs";
        if($where) $sql .= "WHERE $where";
        if($skip!==null) $sql .= " LIMIT $skip,$take";
        else if($take!=null) $sql .= " LIMIT $take";
        return $sql;
    }

    function create_command($sql,...$args){
        $stmt = $this->connection->prepare($sql);
        if(!$stmt) throw new DbException($this->connection->error);
        $cmd= new DbCommand($stmt,$sql);
        if($args) foreach($args as $arg) $cmd->add_parameter($arg);
        return $cmd;
    }

    function dbset(string $entityName):DbSet{
        $clsname = $entityName;
        $filename = $entityName;
        $tbname = $entityName;
        $pos = strpos($entityName,"DbSet");
        if($pos!==false) {
            $filename =$tbname = substr($entityName,0,$pos);
        }else {
            $clsname .= "DbSet";
        }
        $pos = stripos($clsname,"\\");
        if($pos!==false){
            $filename = str_replace("\\","/",$filename).".dbset.php";
            $tbname = substr($tbname , $pos+1);
        }
        if(!class_exists($clsname)){
            $filename = Database::$root_dir."/".$filename;
            if(file_exists($filename))require_once($filename);
        }
        return class_exists($clsname)?new $clsname($this,$tbname):new DbSet($this,$tbname);
    }

    function make_fieldname(string $fname):string{
        return "`$fname`";
    }
    function make_tablename(string $tbname):string{
        return "`$tbname`";
    }

    static function create(){
        return new Database("localhost","root","","YE");
    }

    static $root_dir = __DIR__."/..";
}


class DbCommand{
    public $stmt;
    public $error;
    public $affectedCount;
    public $insertId;
    public $params;
    public $sql;
    function __construct($stmt,string $sql){
        $this->stmt = $stmt;
        
        $this->sql = $sql;
    }
    function add_parameter($value,$type=null){
        if(!$this->params)$this->params=[];
        $this->params[] = new DbParameter($value,$type);
        return $this;
    }

    function execute(){
        $f = "";
        $args = [];
        $args[] = &$f;
        foreach($this->params as $par){
            $type = $par->type;
            if(!$type){
                if(is_string($par->value)) $type= "s";
                else if(is_double($par->value)) $type = "d";
                else if(is_int($par->value))$type="i";
                else if(!$par->value) $type="s";
                else $type = "b";
            }else {
                switch($type){
                    case "string":$type="s";break;
                    case "int":$type="i";break;
                    case "double":$type="d";break;
                    case "float":$type="d";break;
                    default:$type="b";break;
                }
            }
            $f.=$type;
            $args[] = &$par->value;
        }
        call_user_func_array([$this->stmt,"bind_param"],$args);
        $rs = $this->stmt->execute();
        if(!$rs) throw new DbException($this->stmt->error);
        $this->affectedCount = $this->stmt->affected_rows;
        $this->insertId = $this->stmt->insert_id;
        return $this->stmt->get_result();
    }

    
    function query(bool $type=null){
        $rs = $this->execute();
        $rows = [];
        if($rs){
            if($type===null){
                while($row = $rs->fetch_assoc()){
                    $rows[] = $row;
                }
            }else if($type===false){
                while($row = $rs->fetch_row()){
                    $rows[] = $row;
                }
            }else {
                while($row = $rs->fetch_object()){
                    $rows[] = $row;
                }
            }
        }
        return $rows;
    }
    function first(bool $type=null){
        $rs = $this->execute();
        
        if($rs){
            if($type===null){
                return $rs->fetch_assoc();
            }else if($type===false){
                return $rs->fetch_row();
            }else {
                return $rs->fetch_object();
            }
        }
    }

    function close(){
        $this->stmt->close();
    }
}
